package binbase.web.gui

import binbase.web.core.BBBin
import javax.sql.DataSource
import groovy.sql.Sql
import binbase.web.core.BBSpecies
import static binbase.web.QueryUtil.*
import grails.converters.JSON
import org.codehaus.groovy.grails.commons.ConfigurationHolder

/**
 * used for dedicated Bin Queries and there related statistics
 */
class BBBinQueryController {

    DataSource dataSource

    def exportService

    def grailsApplication

    /**
     * shows the binbase bin id
     */
    def showBinByBinBaseBinId = {

        redirect(controller: "BBBin", action: "show", id: "${BBBin.findByBinbaseBinId(params.id).id}")
    }

    /**
     * renders the species and counts for the given bin id
     */
    def ajaxSpeciesForBin = {

        BBBin bin = BBBin.get(params.id)
        def data = []

        Sql sql = Sql.newInstance(dataSource)

        sql.eachRow("""

select a.annotationCount,a.species,b.sampleCount,a.species_id from
(
select
    count(*) as annotationCount, e.name as species,e.id as species_id
from
    bbspectra b,
    bbexperiment_sample c,
    bbexperiment_class d,
    bbspecies e

where
    b.sample_id = c.id and
    c.experiment_class_id = d.id and
    d.species_id = e.id and
    b.bin_id = ${bin.id}

group by
    b.bin_id,
    e.name,
    e.id

) a,
(
select
    count(*) as sampleCount, e.name as species,e.id as species_id
from
    bbexperiment_sample c,
    bbexperiment_class d,
    bbspecies e

where

    c.experiment_class_id = d.id and
    d.species_id = e.id

group by e.name,e.id
) b

where a.species = b.species

        """) {

            data.add([totalSamples: it.sampleCount, species: it.species, foundInSamples: it.annotationCount, species_id: it.species_id])
        }


        render template: "ajaxSpeciesForBin", model: [data: data, bin: bin], plugin: "binbase-web-gui"
    }

    /**
     * renders the organs for the given species and bin
     */
    def ajaxOrgansForSpeciesAndBin = {

        BBBin bin = BBBin.get(params.id)
        BBSpecies species = BBSpecies.get(params.species)

        def data = []

        Sql sql = Sql.newInstance(dataSource)

        sql.eachRow("""

select a.annotationCount,b.sampleCount,a.organ_id as id,a.name as organ from
(
select
    count(b.id) as annotationCount,d.organ_id,e.name
from
    bbspectra b,
    bbexperiment_sample c,
    bbexperiment_class d,
    bborgan e
where
    b.sample_id = c.id and
    c.experiment_class_id = d.id and
    d.species_id = ${species.id} and
    b.bin_id = ${bin.id} and
    e.id = d.organ_id
group by
    b.bin_id,
    e.name,
    d.organ_id
) a,
(
select
    count(c.id) as sampleCount,d.organ_id
from
    bbexperiment_sample c,
    bbexperiment_class d,
    bborgan e
where
    c.experiment_class_id = d.id and
    d.species_id = ${species.id}  and
    e.id = d.organ_id
group by d.organ_id
) b

where a.organ_id = b.organ_id


                """) {

            data.add([totalSamples: it.sampleCount, organ: it.organ, foundInSamples: it.annotationCount, organ_id: it.id, species_id: species.id])
        }

        /**
         * format the data as format and export them
         */
        if (params.format != null) {
            response.contentType = ConfigurationHolder.config.grails.mime.types[params.format]
            response.setHeader("Content-disposition", "attachment; filename=bins.xls")

            def exports = []

            def fields = ["binId", "name", "quantMass", "retentionIndex"]
            def labels = ["binId": "Bin Id", "name": "Name", "quantMass": "Quant Mass", "retentionIndex": "Retention Index"]

            dataToRender.aaData.each {def t ->
                Object o = new Object()
                o.metaClass.binId = t[0]
                o.metaClass.name = t[1]
                o.metaClass.quantMass = t[2]
                o.metaClass.retentionIndex = t[3]

                exports.add(o)
            }

            exportService.export(params.format, response.outputStream, exports, fields, labels, [:], [:])
        }
        /**
         * render the data as json to the controller
         */
        else {
            render template: "ajaxOrgansForSpeciesAndBin", model: [data: data, bin: bin], plugin: "binbase-web-gui"
        }
    }

    /**
     * list all bins
     */
    def ajaxListBinAsJSON = {
        Sql sql = Sql.newInstance(dataSource)

        //result object
        def dataToRender = [:]
        def database = grailsApplication.config.binbase.database.toString()
        dataToRender.aaData = []
        dataToRender.sEcho = params.sEcho
        dataToRender.iTotalRecords = sql.firstRow("select count(id) as id from BBBin where database_id in (select distinct id from bbdatabase where name = ?)", [database]).id
        dataToRender.iTotalDisplayRecords = dataToRender.iTotalRecords

        /**
         * query string for this, will be modified with limiting and sorting options
         */
        if (params."sSearch" == null || params."sSearch".toString().size() == 0) {

            def query = new StringBuffer("""
            select * from BBBin where database_id in (select distinct id from bbdatabase where name = ?)
               """)


            buildQuery(params, query, [
                    "binbase_bin_id",
                    "name",
                    "quant_mass",
                    "retention_index"

            ])

            sql.eachRow(query.toString(), [database], { def bin ->

                dataToRender.aaData << [
                        bin.binbase_bin_id,
                        bin.name,
                        bin.quant_mass,
                        bin.retention_index
                ]

            })
        }
        /**
         * query string with search option
         */
        else {
            def query = new StringBuffer("""
            select * from BBBin where database_id in (select distinct id from bbdatabase where name = ?) and
            (
                lower(name) LIKE ?
             )
               """)


            buildQuery(params, query, [
                    "binbase_bin_id",
                    "name",
                    "quant_mass",
                    "retention_index"

            ])

            sql.eachRow(query.toString(), [database, "%${params."sSearch".toString().toLowerCase()}%".toString()], { def bin ->

                dataToRender.aaData << [
                        bin.binbase_bin_id,
                        bin.name,
                        bin.quant_mass,
                        bin.retention_index
                ]

            })
        }

        /**
         * format the data as format and export them
         */
        if (params.format != null) {
            response.contentType = ConfigurationHolder.config.grails.mime.types[params.format]
            response.setHeader("Content-disposition", "attachment; filename=bins.xls")

            def exports = []

            def fields = ["binId", "name", "quantMass", "retentionIndex"]
            def labels = ["binId": "Bin Id", "name": "Name", "quantMass": "Quant Mass", "retentionIndex": "Retention Index"]

            dataToRender.aaData.each {def t ->
                Object o = new Object()
                o.metaClass.binId = t[0]
                o.metaClass.name = t[1]
                o.metaClass.quantMass = t[2]
                o.metaClass.retentionIndex = t[3]

                exports.add(o)
            }

            exportService.export(params.format, response.outputStream, exports, fields, labels, [:], [:])
        }
        /**
         * render the data as json to the controller
         */
        else {
            render dataToRender as JSON
        }

    }

}
